package com.hex.ds.code.detection.oracle.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.hex.hdtp.common.utils.AntlrUtil;
import com.hex.ds.code.detection.common.entity.ScriptPackScanResultEntity;
import com.hex.ds.code.detection.common.enums.AntlrEnum;
import com.hex.ds.code.detection.hive.service.HiveTableParamSqlVisitor;
import com.hex.ds.code.detection.oracle.gen.PlSqlLexer;
import com.hex.ds.code.detection.oracle.gen.PlSqlParser;
import com.hex.ds.code.detection.oracle.gen.PlSqlParserBaseVisitor;
import lombok.extern.slf4j.Slf4j;
import org.antlr.v4.runtime.CharStreams;
import org.antlr.v4.runtime.CodePointCharStream;
import org.antlr.v4.runtime.CommonTokenStream;
import org.antlr.v4.runtime.tree.ErrorNode;
import org.antlr.v4.runtime.tree.TerminalNode;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Slf4j
public class OracleBasicSqlVisitor extends PlSqlParserBaseVisitor<String> {

    public static final String MARK_BEGIN = "/*";
    public static final String MARK_END = "*/";
    public static final String MARK_CODE = "[CODE]";
    public static final String ERROR_CODE = "[ERROR]";
    public static final String WARN_CODE = "[WARN]";
    public static final String ERROR_MARK_BEGIN = StrUtil.format(" {} {} {}", MARK_BEGIN, MARK_CODE, ERROR_CODE);
    public static final String WARN_MARK_BEGIN = StrUtil.format(" {} {} {}", MARK_BEGIN, MARK_CODE, WARN_CODE);

    // 语法解析异常标识 (遇到一次解析异常之后则打标记)
    private boolean isAnalyticException = false;

    public boolean getIsAnalyticException() {
        return isAnalyticException;
    }

    // 动态配置文件信息
//    private Map<String, Map<String, String>> config = new HashMap<>();
//    private Map<String, Map<String, Object>> objectConfig = new HashMap<>();
    private Map<String,String> config = new HashMap<>();

    private Map<String, ScriptPackScanResultEntity> scriptPackScanResultMaps = new HashMap<>();

    private List<ScriptPackScanResultEntity> scriptPackScanResultList = new ArrayList<>();

    public List<ScriptPackScanResultEntity> getScriptPackScanResultList() {
        return new ArrayList<>(scriptPackScanResultMaps.values());
    }

    // 扫描出的库表名信息
    private List<String> databaseNameList = new ArrayList<>();

    public List<String> getDatabaseNameList() {
        return databaseNameList;
    }

    // SQL切段
    private List<String> splitSql = new ArrayList<>();

    public List<String> getSplitSql() {
        return splitSql;
    }

    // insert-value 扫描中返回标记后的SQL映射关系
    private Map<String, String> remarkSqlMap = new HashMap<>();

    public Map<String, String> getRemarkSqlMap() {
        return remarkSqlMap;
    }

    //public String visitRoot(final String sourceSql, Map<String, ScriptPackScanResultEntity> scriptPackScanResults, Map<String, Map<String, String>> config, Map<String, Map<String, Object>> objectConfig) {
//    public String visitRoot(final String sourceSql, Map<String, ScriptPackScanResultEntity> scriptPackScanResults, Map<String, Map<String, String>> config, Map<String, Map<String, Object>> objectConfig) {
    public String visitRoot(final String sourceSql, Map<String, ScriptPackScanResultEntity> scriptPackScanResults, Map<String,String> config) {
        this.scriptPackScanResultMaps = scriptPackScanResults;
        this.config = config;
        // 初始化错误标记后的sql
//        this.objectConfig = objectConfig;
        final CodePointCharStream input = CharStreams.fromString(sourceSql);
        PlSqlLexer lexer = new PlSqlLexer(input);
        CommonTokenStream token = new CommonTokenStream(lexer);
        PlSqlParser parser = new PlSqlParser(token);
        String resultStr = this.visit(parser.sql_script());
        return resultStr;
    }

    public String visitRoot(final String sourceSql) {
        final CodePointCharStream input = CharStreams.fromString(sourceSql);
        PlSqlLexer lexer = new PlSqlLexer(input);
        CommonTokenStream token = new CommonTokenStream(lexer);
        PlSqlParser parser = new PlSqlParser(token);
        return this.visit(parser.sql_script());
    }

    @Override
    public String visitInsert_statement(PlSqlParser.Insert_statementContext ctx) {
        String resultStr = super.visitInsert_statement(ctx);
        try {
            if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
                String[] insertInfo = visitInsert_into_clause(ctx.single_table_insert().insert_into_clause()).split(";");
                String valueInfo = visitValues_clause(ctx.single_table_insert().values_clause());
                scanInsert(insertInfo[0], insertInfo[1] , valueInfo);
            }
        } catch (Exception e) {
            log.error("【antlr处理异常】visitInsert_statement出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitInsert_statement出现异常" + e.getMessage(), e);
        }
        return resultStr;
    }

    @Override
    public String visitInsert_into_clause(PlSqlParser.Insert_into_clauseContext ctx) {
        try {
            databaseNameList.add(ctx.general_table_ref().getText());
            if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
                visitChildren(ctx);
                return ctx.general_table_ref().getText() + ";" + ctx.paren_column_list().column_list().getText();
            } else {
                return super.visitInsert_into_clause(ctx);
            }
        } catch (Exception e) {
            log.error("【antlr处理异常】visitInsert_into_clause出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitInsert_into_clause出现异常" + e.getMessage(), e);
        }
    }

    @Override
    public String visitAlter_table(PlSqlParser.Alter_tableContext ctx) {
        try {
            databaseNameList.add(ctx.tableview_name().getText());
        } catch (Exception e) {
            databaseNameList.add("");
        }
        return super.visitAlter_table(ctx);
    }

    @Override
    public String visitValues_clause(PlSqlParser.Values_clauseContext ctx) {
        try {
            if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
                visitChildren(ctx);
                return ctx.getText();
            } else {
                return super.visitValues_clause(ctx);
            }
        } catch (Exception e) {
            log.error("【antlr处理异常】visitValues_clause出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitValues_clause出现异常" + e.getMessage(), e);
        }
    }

    @Override
    public String visitUpdate_statement(PlSqlParser.Update_statementContext ctx) {
        try {
            databaseNameList.add(ctx.general_table_ref().getText());
            if (scriptPackScanResultMaps.containsKey(AntlrEnum.VALUE_DB_NAME.getValue())) {
                scanUpdate(ctx.general_table_ref().dml_table_expression_clause().getText(), ctx.update_set_clause().getText());
            }
        } catch (Exception e) {
            log.error("【antlr处理异常】visitUpdate_statement出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitUpdate_statement出现异常" + e.getMessage(), e);
        }
        return super.visitUpdate_statement(ctx);
    }

    @Override
    public String visitDelete_statement(PlSqlParser.Delete_statementContext ctx) {
        String resultStr = super.visitDelete_statement(ctx);
        databaseNameList.add(ctx.general_table_ref().getText());
        // 校验 不允许存在删除语句
        resultStr = deleteSqlScan(resultStr);
        return resultStr;
    }

    @Override
    public String visitNon_reserved_keywords_pre12c(PlSqlParser.Non_reserved_keywords_pre12cContext ctx) {
        try {
            if (scriptPackScanResultMaps.containsKey(AntlrEnum.COMMIT_CHECK.getValue()) && null != ctx.COMMIT()) {
                setScriptPackScanResults(AntlrEnum.COMMIT_CHECK.getValue(), "COMMIT", "default", true, "necessary");
            }
        } catch (Exception e) {
            log.error("【antlr处理异常】visitNon_reserved_keywords_pre12c出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitNon_reserved_keywords_pre12c出现异常" + e.getMessage(), e);
        }
        return super.visitNon_reserved_keywords_pre12c(ctx);
    }

    @Override
    public String visitUnit_statement(PlSqlParser.Unit_statementContext ctx) {
        try {
            String pieceSql = AntlrUtil.assembleChildren(ctx);
            splitSql.add(pieceSql);
        } catch (Exception e) {
            log.error("【antlr处理异常】visitUnit_statement出现异常: {}", e.getMessage(), e);
            throw new RuntimeException("【antlr处理异常】visitUnit_statement出现异常" + e.getMessage(), e);
        }
        return super.visitUnit_statement(ctx);
    }

    @Override
    public String visitErrorNode(ErrorNode node) {
        // 错误标记只打一次(因为语法错误会导致后续行多处解析异常)
        if (!isAnalyticException) {
            isAnalyticException = true;
            String resultStr = node.getText();
            return getMarkResult(AntlrEnum.ERROR_SQL.getDesc(), "necessary") + resultStr;
        } else {
            return node.getText();
        }
    }

    /**
     * @Method deleteSqlScan
     * @Param
     * @param resultStr
     * @Return java.lang.String
     * @Description 删除语句存在性检查
     * @Author Wang zhihao
     * @Date 2024/2/6 16:38
     * @Version V1.0
     */
    private String deleteSqlScan(String resultStr) {
        if (scriptPackScanResultMaps.containsKey(AntlrEnum.DELETE_CHECK.getValue())) {
            setScriptPackScanResults(AntlrEnum.DELETE_CHECK.getValue(), "DELETE", "default", false, "necessary");
            return getMarkResult(AntlrEnum.DELETE_CHECK.getDesc(), "necessary") + resultStr;
        }
        return resultStr;
    }

    private void scanUpdate(String dbTableName, String setColumnClause) {
        String columnClause = StrUtil.strip(setColumnClause, "set").trim();
        List<String> columnList = Arrays.stream(columnClause.split(",")).map(String::trim).collect(Collectors.toList());
        Map<String, String> columnMap = columnList.stream().map(s -> s.split("=")).collect(Collectors.toMap(s -> s[0], s -> s[1]));
        String tableName = dbTableName.contains(".") ? dbTableName.split("\\.")[1] : dbTableName;
        matchScan(tableName, columnMap);
    }

    /**
     * @Method scanInsert
     * @Param
     * @param dbTableName
     * @param insertInfo
     * @param valueInfo
     * @Return void
     * @Description 扫描检查insert-value语句
     * @Author Wang zhihao
     * @Date 2023/10/22 17:50
     * @Version V1.0
     */
    private void scanInsert(String dbTableName, String insertInfo, String valueInfo) {
        String tableName = dbTableName.contains(".") ? dbTableName.split("\\.")[1] : dbTableName;
        List<String> insertInfos = Arrays.stream(insertInfo.split(",")).map(String::trim).collect(Collectors.toList());
        List<String> valueInfos = splitIgnoringCommaBetweenSingleQuotes(StrUtil.stripIgnoreCase(valueInfo.trim(),"VALUES(", ")"));
        Map<String, String> insertValueMaps = IntStream.range(0, insertInfos.size())
                .collect(HashMap::new,(m,i)->m.put(insertInfos.get(i),valueInfos.get(i)),(m,n)->{});
        if (insertInfos.size() != valueInfos.size()) {
            throw new RuntimeException("扫描检查insert-value语句：字段数量与值的数量不对应");
        }
        // 根据表名匹配扫描检查
        matchScan(tableName, insertValueMaps);
    }

    @Override
    public String visitExpressions(PlSqlParser.ExpressionsContext ctx) {
        return super.visitExpressions(ctx);
    }

    @Override
    public String visitTerminal(TerminalNode node) {
        if (!node.getText().equals("<EOF>")) {
            return node.getText();
        }
        return super.visitTerminal(node);
    }

    /**
     * @Method splitIgnoringCommaBetweenSingleQuotes
     * @Param
     * @param str
     * @Return java.util.List<java.lang.String>
     * @Description 分割时忽略单引号之间的逗号
     * @Author Wang zhihao
     * @Date 2024/2/18 9:50
     * @Version V1.0
     */
    public static List<String> splitIgnoringCommaBetweenSingleQuotes(String str) {
        Pattern pattern = Pattern.compile(",(?=(?:[^'\"]*['\"][^'\"]*['\"])*[^'\"]*$)");
        List<String> parts = Arrays.stream(pattern.split(str)).map(s -> StrUtil.strip(s, "'", "'")).collect(Collectors.toList());
        return parts;
    }

    /**
     * @Method matchScan
     * @Param
     * @param tableName
     * @Return void
     * @Description 根据表名匹配扫描检查
     * @Author Wang zhihao
     * @Date 2023/10/11 16:20
     * @Version V1.0
     */
    private void matchScan(String tableName, Map<String, String> insertValueMaps) {
        switch (tableName.toUpperCase()) {
            case "CTL_LOAD_CONFIG":
                scanCtlLoad(tableName, insertValueMaps);
                break;
            case "CTL_AUDIT_PARAM":
            case "CTL_AUDIT_PARAM_NEW":
                scanCtlAudit(tableName, insertValueMaps);
                break;
            case "TPUSHLIST":
                scanCtlConfig(tableName, insertValueMaps);
                break;
            default:
                break;
        }
    }

    /**
     * @Method scanCtlLoad
     * @Param
     * @Return void
     * @Description 下载文件扫描
     * @Author Wang zhihao
     * @Date 2023/10/11 16:27
     * @Version V1.0
     */
    private void scanCtlLoad(String tableName, Map<String, String> insertValueMaps) {
        if (insertValueMaps.containsKey("POST_SQL_IMPALA")) {
            checkSqlLoadAuditColumnParametric(tableName, insertValueMaps.get("POST_SQL_IMPALA"));
        }
        if (insertValueMaps.containsKey("TRG_SCHEMA")) {
            checkTableLoadAuditColumnParametric(tableName, insertValueMaps.get("TRG_SCHEMA"));
        }
    }

    /**
     * @Method scanCtlAudit
     * @Param
     * @Return void
     * @Description 稽核文件扫描
     * @Author Wang zhihao
     * @Date 2023/10/11 16:28
     * @Version V1.0
     */
    private void scanCtlAudit(String tableName, Map<String, String> insertValueMaps) {
        if (insertValueMaps.containsKey("SRC_SQL")) {
            checkSqlLoadAuditColumnParametric(tableName, insertValueMaps.get("SRC_SQL"));
        }
        if (insertValueMaps.containsKey("TRG_SQL")) {
            checkSqlLoadAuditColumnParametric(tableName, insertValueMaps.get("TRG_SQL"));
        }
    }

    /**
     * @Method scanCtlConfig
     * @Param
     * @Return void
     * @Description 推数文件扫描
     * @Author Wang zhihao
     * @Date 2023/10/11 16:28
     * @Version V1.0
     */
    private void scanCtlConfig(String tableName, Map<String, String> insertValueMaps) {
        if (insertValueMaps.containsKey("C_PUSHSQL")) {
            checkSqlLoadAuditColumnParametric(tableName, insertValueMaps.get("C_PUSHSQL"));
        }
        if (insertValueMaps.containsKey("C_INCSQL")) {
            checkSqlLoadAuditColumnParametric(tableName, insertValueMaps.get("C_INCSQL"));
        }
        if (insertValueMaps.containsKey("C_TASQL")) {
            checkSqlLoadAuditColumnParametric(tableName, insertValueMaps.get("C_TASQL"));
        }
    }

    /**
     * @Method checkColumnParametric
     * @Param
     * @Return void
     * @Description 检验下载与稽核文件中字段是否参数化-仅schema
     * @Author Wang zhihao
     * @Date 2023/10/11 18:16
     * @Version V1.0
     */
    private void checkTableLoadAuditColumnParametric(String tableName, String columnValue) {
//        List<String> serviceParam = JsonReadUtil.castList(objectConfig.get("serviceParam_ctlLoadConfig"), String.class);
        String[] serviceParam_ctlLoadConfigs = config.get("serviceParam_ctlLoadConfig").split(",");
        List<String> list = Arrays.asList(serviceParam_ctlLoadConfigs);
        List<String> serviceParam = new ArrayList<>();
        for (int i = 0;i<list.size();i++){
            serviceParam.add(list.get(i).trim());
        }
        if (StrUtil.isNotBlank(columnValue) && CollectionUtil.isNotEmpty(serviceParam) && !CollectionUtil.contains(serviceParam, columnValue)) {
            setScriptPackScanResults(AntlrEnum.VALUE_DB_NAME.getValue(), "INSERT INTO", tableName, false, "necessary");
        }
    }

    /**
     * @Method checkColumnParametric
     * @Param
     * @Return void
     * @Description 检验下载与稽核文件中字段是否参数化-SQL段
     * @Author Wang zhihao
     * @Date 2023/10/11 18:16
     * @Version V1.0
     */
    private void checkSqlLoadAuditColumnParametric(String tableName, String columnValue) {
        if (StrUtil.equalsIgnoreCase(columnValue, "null")) {
            return;
        }
        String sourceSql = columnValue;
        // 处理SQL中的特殊转译字符
        columnValue = dealColumnValue(columnValue);
        try {
            HiveTableParamSqlVisitor hiveTableParamSqlVisitor = new HiveTableParamSqlVisitor();
            String resultStr = hiveTableParamSqlVisitor.visitRoot(tableName, columnValue, config);
            // 用于记录推送下载稽核脚本中嵌套的SQL处理结果
            remarkSqlMap.put(sourceSql, resultStr);
            setScriptPackScanResults(AntlrEnum.VALUE_DB_NAME.getValue(), "INSERT INTO", tableName, hiveTableParamSqlVisitor.getResult(), "necessary");
            log.info(StrUtil.format("【推送下载稽核】到【{}】表,value中存在Impala语句", tableName));
        } catch (Exception e) {
            OracleTableParamSqlVisitor oracleTableParamSqlVisitor = new OracleTableParamSqlVisitor();
            String resultStr = oracleTableParamSqlVisitor.visitRoot(tableName, columnValue, config);
            // 是否通过参数化检测
            boolean isCheckPass = oracleTableParamSqlVisitor.getResult();
            // 是否存在语法异常
            boolean isAnalyticException = oracleTableParamSqlVisitor.getIsAnalyticException();
            // 用于记录推送下载稽核脚本中嵌套的SQL处理结果
            remarkSqlMap.put(sourceSql, resultStr);
            setScriptPackScanResults(AntlrEnum.VALUE_DB_NAME.getValue(), "INSERT INTO", tableName, isCheckPass && !isAnalyticException, "necessary");
            log.info(StrUtil.format("【推送下载稽核】到【{}】表,value中存在Oracle语句", tableName));
        }
    }

    /**
     * @Method dealColumnValue
     * @Param
     * @param sourceSql
     * @Return java.lang.String
     * @Description 处理SQL中的特殊转译字符
     * @Author Wang zhihao
     * @Date 2024/1/19 10:30
     * @Version V1.0
     */
    private String dealColumnValue(String sourceSql) {
        String regex = "''(.*?)''";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sourceSql);
        return matcher.replaceAll("'$1'");
    }

    /**
     * @Method setScriptPackScanResults
     * @Param
     * @param scanName
     * @param sqlType
     * @param scanTable
     * @param scanState
     * @Return void
     * @Description 配置返回参数
     * @Author Wang zhihao
     * @Date 2023/10/7 10:43
     * @Version V1.0
     */
    private void setScriptPackScanResults(String scanName, String sqlType, String scanTable, boolean scanState, String level) {
        scriptPackScanResultMaps.get(scanName).setSqlType(sqlType);
        scriptPackScanResultMaps.get(scanName).setScanTable(scanTable);
        String lastScanState = scriptPackScanResultMaps.get(scanName).getScanState();
        boolean countScanSate = scanState;
        if (StrUtil.isNotEmpty(scriptPackScanResultMaps.get(scanName).getScanState())) {
            countScanSate = StrUtil.equalsIgnoreCase(lastScanState, "success") && scanState;
        }
        scriptPackScanResultMaps.get(scanName).setScanState(countScanSate ? "success" : (level.equalsIgnoreCase("necessary") ? "fail" : "warning"));
    }

    /**
     * @Method getMarkResult
     * @Param
     * @param scanName
     * @param scanLevel
     * @Return java.lang.String
     * @Description 获取SQL中的不同等级异常标记信息
     * @Author Wang zhihao
     * @Date 2024/2/6 13:34
     * @Version V1.0
     */
    //todo 优化或许做成一个实体对象来获取
    private String getMarkResult(String scanName, String scanLevel) {
        switch (scanLevel.toUpperCase(Locale.ROOT)) {
            case "WARNING":
                return StrUtil.format("{} {} {}", WARN_MARK_BEGIN, scanName, MARK_END);
            case "NECESSARY":
                return StrUtil.format("{} {} {}", ERROR_MARK_BEGIN, scanName, MARK_END);
            default:
                return "";
        }
    }

}
